package uk.ac.shef.ecomm.jdbc.mysql;

import java.sql.PreparedStatement;
import java.sql.SQLException;

import uk.ac.shef.ecomm.*;



public class ManageProjects extends mysqlDataAccess
{
	private PreparedStatement sqlInserProject;
	private PreparedStatement sqlInsertPartsInProj;
	private PreparedStatement sqlUpdatePartsStatInProj;
	
	public ManageProjects()
	{
		try
		{
			connect();
			sqlInserProject = connection.prepareStatement("INSERT into " + ProjectsTBL + " (name,res_email,description,status,lang,minAge,maxAge,gender,smoker,maxPart,freetext) VALUES (?,?,?,?,?,?,?,?,?,?,?)");
			sqlInsertPartsInProj = connection.prepareStatement("INSERT into " + PartsInProjTBL + " (proj_id,part_email,part_status) VALUES (?,?,?)");
			sqlUpdatePartsStatInProj = connection.prepareStatement("UPDATE " + PartsInProjTBL + " SET part_status= ? WHERE proj_id=? and part_email=?");
		}
		catch (Exception ex)
		{
			ex.printStackTrace();
		}
	}
	
	public boolean CreateProj(Project p)
	{
		try
		{
			int result;
			
			sqlInserProject.setString(1, p.getName());
			sqlInserProject.setString(2, p.getRes_email());
			sqlInserProject.setString(3, p.getDescription());
			sqlInserProject.setString(4, p.getStatus());
			sqlInserProject.setString(5, p.getRequirments().getLanguage());
			sqlInserProject.setInt(6, p.getRequirments().getMinAge());
			sqlInserProject.setInt(7, p.getRequirments().getMaxAge());
			sqlInserProject.setString(8, p.getRequirments().getGender());
			
			if (p.getRequirments().isSmoker() == true)
			{
				sqlInserProject.setString(9, "true");
			}
			else
			{
				sqlInserProject.setString(9, "false");
			}
			sqlInserProject.setInt(10, p.getRequirments().getNumOfParticipants());
			sqlInserProject.setString(11, p.getRequirments().getFreeTextReq());
			
			result = sqlInserProject.executeUpdate();
			if (result == 0)
			{
				connection.rollback();
				return false;
			}
			
			connection.commit();
			return true;
			
		}
		catch (SQLException sqle)
		{
			sqle.printStackTrace();
			return false;
		}	
	}
	
	public boolean AddParticipantToProject(Participant pt, Project pj)
	{
		try
		{
			int result;
			
			sqlInsertPartsInProj.setInt(1, pj.getId());
			sqlInsertPartsInProj.setString(2,pt.getEmail());
			sqlInsertPartsInProj.setString(3, "selected");
			
			result = sqlInsertPartsInProj.executeUpdate();
			if (result == 0)
			{
				connection.rollback();
				return false;
			}
			
			connection.commit();
			return true;
			
		}
		catch (com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException sqle)
		{
			System.out.println(sqle.toString());
			return false;
			
		}
		catch (SQLException sqle)
		{
			sqle.printStackTrace();
			return false;
		}
		
	}
	public boolean UpdateParticipantStatInProject(Participant pt, Project pj, String status)
	{
		try
		{
			int result;
			
			sqlUpdatePartsStatInProj.setString(1, status);
			sqlUpdatePartsStatInProj.setInt(2,pj.getId());
			sqlUpdatePartsStatInProj.setString(3,pt.getEmail());
			
			result = sqlUpdatePartsStatInProj.executeUpdate();
			if (result == 0)
			{
				connection.rollback();
				return false;
			}
			
			connection.commit();
			return true;
			
		}
		catch (com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException sqle)
		{
			System.out.println(sqle.toString());
			return false;
			
		}
		catch (SQLException sqle)
		{
			sqle.printStackTrace();
			return false;
		}
	}
	public void close()
	{
		try
		{
			sqlInserProject.close();
			sqlInsertPartsInProj.close();
			sqlUpdatePartsStatInProj.close();
			connection.close();
		}
		catch (SQLException sqle)
		{
			sqle.printStackTrace();
		}
	}
}
